package com.bj58.ecdata.calc.dbservice.dao;

import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.bj58.ecdata.calc.dbservice.dao.annotation.Dimension;
import com.bj58.ecdata.calc.dbservice.dao.annotation.PrimaryDimension;
import com.bj58.ecdata.calc.dbservice.dao.annotation.TableName;
import com.bj58.ecdata.calc.dbservice.dao.helper.BaseJdbcHelper;
import com.bj58.ecdata.calc.dbservice.dao.model.FieldInfo;
import com.bj58.ecdata.util.DateUtils;
import com.bj58.ecdata.util.ReflectUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;

/**
 * mysql相关操作
 * @author zhaoxiang
 *
 * @param <T>
 */
public abstract class JdbcBaseDao<T> implements ParameterizedRowMapper<T>,ResultSetExtractor<List<T>>, InitializingBean {

	@Autowired
	@Qualifier("jdbcTemplate")
	protected NamedParameterJdbcTemplate jdbcTemplate;

	protected Class<T> entityClass;//model

	protected PropertyDescriptor[] propertyDescriptors;

	protected Map<String, FieldInfo> fieldInfos = Maps.newHashMap(); // 所有的列信息

	protected FieldInfo primaryDimension; // 主维度

	protected List<FieldInfo> dimensions = new ArrayList<FieldInfo>(); // 维度（不包含主维度）

	protected BaseJdbcHelper jdbcHelper;

	protected String tableName;
	
	protected String createSQL;
	
	protected final Set<String> tableNameSet = Sets.newConcurrentHashSet();
	
	public static String[] keywords = new String[] { "ADD", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ASENSITIVE", "BEFORE", "BETWEEN",
			"BIGINT", "BINARY", "BLOB", "BOTH", "BY", "CALL", "CASCADE", "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE", "COLUMN",
			"CONDITION", "CONNECTION", "CONSTRAINT", "CONTINUE", "CONVERT", "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP",
			"CURRENT_USER", "CURSOR", "DATABASE", "DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND", "DEC", "DECIMAL",
			"DECLARE", "DEFAULT", "DELAYED", "DELETE", "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT", "DISTINCTROW", "DIV", "DOUBLE", "DROP",
			"DUAL", "EACH", "ELSE", "ELSEIF", "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN", "FALSE", "FETCH", "FLOAT", "FLOAT4", "FLOAT8",
			"FOR", "FORCE", "FOREIGN", "FROM", "FULLTEXT", "GOTO", "GRANT", "GROUP", "HAVING", "HIGH_PRIORITY", "HOUR_MICROSECOND", "HOUR_MINUTE",
			"HOUR_SECOND", "IF", "IGNORE", "IN", "INDEX", "INFILE", "INNER", "INOUT", "INSENSITIVE", "INSERT", "INT", "INT1", "INT2", "INT3", "INT4",
			"INT8", "INTEGER", "INTERVAL", "INTO", "IS", "ITERATE", "JOIN", "KEY", "KEYS", "KILL", "LABEL", "LEADING", "LEAVE", "LEFT", "LIKE",
			"LIMIT", "LINEAR", "LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LONG", "LONGBLOB", "LONGTEXT", "LOOP", "LOW_PRIORITY",
			"MATCH", "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND", "MINUTE_SECOND", "MOD", "MODIFIES", "NATURAL",
			"NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "ON", "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER", "OUT", "OUTER", "OUTFILE",
			"PRECISION", "PRIMARY", "PROCEDURE", "PURGE", "RAID0", "RANGE", "READ", "READS", "REAL", "REFERENCES", "REGEXP", "RELEASE", "RENAME",
			"REPEAT", "REPLACE", "REQUIRE", "RESTRICT", "RETURN", "REVOKE", "RIGHT", "RLIKE", "SCHEMA", "SCHEMAS", "SECOND_MICROSECOND", "SELECT",
			"SENSITIVE", "SEPARATOR", "SET", "SHOW", "SMALLINT", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING",
			"SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS", "SQL_SMALL_RESULT", "SSL", "STARTING", "STRAIGHT_JOIN", "TABLE", "TERMINATED", "THEN",
			"TINYBLOB", "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRIGGER", "TRUE", "UNDO", "UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE",
			"USAGE", "USE", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP", "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING", "WHEN",
			"WHERE", "WHILE", "WITH", "WRITE", "X509", "XOR", "YEAR_MONTH", "ZEROFILL" };

	@Override
	public T mapRow(ResultSet rs, int num) throws SQLException {
		try {
       		T entity = entityClass.newInstance();
			for (FieldInfo info : fieldInfos.values()) {
				String dbName = info.getDbName().toLowerCase();
				if (dbName.startsWith("`") && dbName.endsWith("`")) {
					dbName = dbName.substring(1, dbName.length() - 1);
				}
				Method writeMethod = info.getWriteMethod();
				Type type = info.getField().getGenericType();
				writeMethod.invoke(entity, ReflectUtil.getValueFormRsByType(type, rs, dbName));
			}
			return entity;
		} catch (Exception e) {
			throw new SQLException(e);
		}
	}
	
	@Override
	public List<T> extractData(ResultSet rs) throws SQLException {
		try {
			List<T> results = Lists.newArrayList();

	       	ResultSetMetaData rsmd = rs.getMetaData();
	        int columnsCount = rsmd.getColumnCount();
	        Set<String> columnNameSet = Sets.newHashSet();
	        for(int i = 1; i <= columnsCount; i++)
	        	columnNameSet.add(rsmd.getColumnLabel(i).toLowerCase());
	        while (rs.next()){
				T entity = entityClass.newInstance();
				for (FieldInfo info : fieldInfos.values()) {
					String dbName = info.getDbName().toLowerCase();
					if(columnNameSet.contains(dbName)){
						if (dbName.startsWith("`") && dbName.endsWith("`")) {
							dbName = dbName.substring(1, dbName.length() - 1);
						}
						Method writeMethod = info.getWriteMethod();
						Type type = info.getField().getGenericType();
						writeMethod.invoke(entity, ReflectUtil.getValueFormRsByType(type, rs, dbName));
					}
				}
				results.add(entity);
	        }
			return results;
		} catch (Exception e) {
			throw new SQLException(e);
		}
	}

	
	public FieldInfo getFieldInfo(String fieldName) {
		return fieldInfos.get(fieldName);
	}

	/**
	 * 覆盖InitializingBean的方法，类实例化后自动调用
	 * 获取泛型类的类型，字段，注解等信息。
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void afterPropertiesSet() throws Exception {

		entityClass = ReflectUtil.getGenericType(this.getClass(), 0);
		if (entityClass == null)
			throw new IllegalStateException("EntityClass is error");

		TableName tm = entityClass.getAnnotation(TableName.class);
		this.tableName = tm.value();

		propertyDescriptors = Introspector.getBeanInfo(entityClass).getPropertyDescriptors();

		for (PropertyDescriptor one : propertyDescriptors) {
			String fieldName = one.getName();
			if (!"class".equals(fieldName)) {
				Field field = ReflectUtil.getFieldByName(fieldName, entityClass);
				if (field != null) {
					String name = one.getName();
					String dbName = name;
					Method writeMethod = one.getWriteMethod();
					Method readMethod = one.getReadMethod();

					if (Arrays.asList(keywords).contains(dbName.toUpperCase())) {

						dbName = "`" + dbName + "`";
					}

					PrimaryDimension pd = field.getAnnotation(PrimaryDimension.class);
					Dimension d = field.getAnnotation(Dimension.class);

					FieldInfo fieldInfo = new FieldInfo(field, name, dbName, writeMethod, readMethod);
					if (null != pd)
						primaryDimension = fieldInfo;
					else if (null != d)
						dimensions.add(fieldInfo);

					fieldInfos.put(fieldName, fieldInfo);

				}
			}
		}
		
		//对应的mysql表的 创建表语句，由子类提供
		createSQL = this.getCreateSQL();
		this.init_jdbc_helper();
		/* 加载model对应的所有表名，按天切分 */
		this.loadTableNameSet();
	}
	
	private void loadTableNameSet() {
		String sql = "SHOW TABLES like '"+tableName+"%';";
		Map<String, String> paramsMap = null;
		List<String> list = jdbcTemplate.queryForList(sql, paramsMap, String.class);
		tableNameSet.clear();
		tableNameSet.addAll(list);
	}
	
	/**
	 * 设置创建表SQL,由具体子类实现
	 */
	protected abstract String getCreateSQL();
	
	/**
	 * 创建表
	 */
	protected  void createTable(String name){
		String sql = String.format(createSQL, name);
		Map<String, String> paramMap = null;
		jdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 删除表,根据给定日期
	 */
	protected  int dropTable(Date date){
		String day = DateUtils.dateFormat(date, "yyyyMMdd");
		String curTableName = String.format("%s_%s", this.tableName,day);
		String sql = "DROP TABLE IF EXISTS %s ;";
		Map<String, String> paramMap = null;
		jdbcTemplate.update(String.format(sql, curTableName), paramMap);
		this.tableNameSet.remove(curTableName);
		return 1;
	}
	
	private void init_jdbc_helper() {
		String columns = null;
		String insertBeanColumns = null;

		StringBuffer columnsBuffer = new StringBuffer(" ");
		StringBuffer insertBeanColumnsBuffer = new StringBuffer(" ");

		for (FieldInfo one : fieldInfos.values()) {
			String dbName = one.getDbName();
			String name = one.getName();
			columnsBuffer.append(dbName).append(",");
			insertBeanColumnsBuffer.append(":").append(name).append(",");
		}
		columns = columnsBuffer.substring(0, columnsBuffer.length() - 1) + " ";
		insertBeanColumns = insertBeanColumnsBuffer.substring(0, insertBeanColumnsBuffer.length() - 1) + " ";
		jdbcHelper = new BaseJdbcHelper(columns, insertBeanColumns);
		StringBuffer whereDimensionBuffer = new StringBuffer(" ");
		if (primaryDimension != null)
			whereDimensionBuffer.append(primaryDimension.getDbName()).append("=:").append(primaryDimension.getName());
		if (dimensions.size() > 0) { 
			for (FieldInfo dimension : dimensions) {
				whereDimensionBuffer.append(" and ").append(dimension.getDbName()).append("=:").append(dimension.getName());
			}
		}
		String whereDimension = whereDimensionBuffer.length() > 1 ? whereDimensionBuffer.toString() : null;
		jdbcHelper.setWhere_dimensions(whereDimension);
	}

	//插入一条记录
	protected T jdbc_insert(T model) throws Exception {
		long time = (Long) primaryDimension.getReadMethod().invoke(model);
		String curTableName = String.format("%s_%s", this.tableName,DateUtils.dateFormat(time,"yyyyMMdd")); 
		boolean hasTable = tableNameSet.contains(curTableName);
		if(!hasTable)
		{ 
			synchronized (this.tableNameSet) {
				if(!tableNameSet.contains(curTableName)){
					createTable(curTableName);
					tableNameSet.add(curTableName);
				}
			}
		}
		String sql = String.format("insert ignore into %s (%s) values( %s )",curTableName, this.jdbcHelper.getColumns(),
				this.jdbcHelper.getInsert_bean_columns());
		int ret = this.jdbcTemplate.update(sql, new BeanPropertySqlParameterSource(model));
		return ret > 0 ? model : null;
	}

	/**
	 * 根据时间戳从mysql查数据
	 * @throws Exception
	 */
	public List<T> jdbc_load(T model) throws Exception {
		long time = (Long) primaryDimension.getReadMethod().invoke(model);
		
		String curTableName = String.format("%s_%s", this.tableName,DateUtils.dateFormat(time,"yyyyMMdd")); 

		String sql = "select %s from %s where " + this.jdbcHelper.getWhere_dimensions();
		List<T> res = Collections.emptyList();
		try {
			sql = String.format(sql, this.jdbcHelper.getColumns(), curTableName);

			List<T> list = this.jdbcTemplate.query(sql, new BeanPropertySqlParameterSource(model), (ParameterizedRowMapper<T>)this);
			if (list != null && list.size() > 0) {
				res = list;
			}
		} catch (Exception e) {
			throw new DataAccessResourceFailureException("", e);
		}
		return res;
	}

	/**
	 * 根据sql查数据
	 * @param params: <参数名，值>
	 * @throws DataAccessException
	 */
	public List<T> jdbc_query(String sql, Map<String, Object> params) throws DataAccessException {
		return this.jdbcTemplate.query(sql, params, (ResultSetExtractor<List<T>>)this);
	}
	
	/**
	 * 根据sql查数据
	 * @throws DataAccessException
	 */
	public List<T> jdbc_query(String sql) throws DataAccessException {
		return this.jdbcTemplate.query(sql, (ResultSetExtractor<List<T>>)this); //new BeanPropertyRowMapper(User.class)
	}

	protected int jdbc_update(String sql, Map<String, ?> paramMap) {
		return jdbcTemplate.update(sql, paramMap);
	}

	/**
	 * @return the jdbcTemplate
	 */
	public NamedParameterJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate
	 *            the jdbcTemplate to set
	 */
	public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}
